<?php
defined('BASEPATH') OR exit('No direct script access allowed');

/**
 * 股票数据模型
 */
class Stocks extends CI_model {

	/**
	 * 检查是否存在股票名称表，如果存在，则清除，否则，新建
	 * @return [type] [description]
	 */
	public function resetStocksTable() {
		$this->load->dbforge();
		$this->dbforge->drop_table('stocks', true);
		return $this->creatStock();
	}
	/**
	 * 创建股票代码表
	 */
	public function creatStock() {
		// 载入数据库维护类:
		$this->load->dbforge();
		// 创建字段
		$fields = array(
			'sid' => array(
				'type' => 'INT',
				'constraint' => 5,
				'unsigned' => TRUE,
				'auto_increment' => TRUE,
			),
			'symbol' => array(
				'type' => 'varchar(20)',
				'null' => FALSE,
			),
			'scode' => array(
				'type' => 'varchar(20)',
				'null' => FALSE,
			),
			'sname' => array(
				'type' => 'varchar(20)',
				'null' => FALSE,
			),
			'isStock' => array(
				'type' => 'tinyint(1)',
				'null' => FALSE,
				'default' => 1,
			),
			'isSelected' => array(
				'type' => 'tinyint(1)',
				'null' => FALSE,
				'default' => 0,
			),
			'noTable' => array(
				'type' => 'tinyint(1)',
				'null' => FALSE,
				'default' => 0,
			),
			'sustainedGrowth' => array(
				'type' => 'tinyint(3)',
				'unsigned' => TRUE,
				'null' => FALSE,
				'default' => 0,
			),
			'xsmlv' => array(
				'type' => 'decimal(10,2)',
				'null' => TRUE,
			),
			'jlrhjl' => array(
				'type' => 'decimal(10,2)',
				'null' => TRUE,
			),
			'lowpb' => array(
				'type' => 'decimal(3,1)',
				'null' => TRUE,
			),
			'toppb' => array(
				'type' => 'decimal(3,1)',
				'null' => TRUE,
			),
			'gridInterval' => array(
				'type' => 'tinyint(2)',
				'unsigned' => TRUE,
				'null' => FALSE,
				'default' => 3,
			),
			'mgjzc' => array(
				'type' => 'decimal(10,2)',
				'null' => TRUE,
			),
			'roe' => array(
				'type' => 'decimal(10,2)',
				'null' => TRUE,
			),
		);
		$this->dbforge->add_field($fields);
		// 创建主键
		$this->dbforge->add_key('sid', TRUE);
		// 创建索引
		$this->dbforge->add_key('isStock');
		// 创建索引
		$this->dbforge->add_key('noTable');
		// 创建索引
		$this->dbforge->add_key('sustainedGrowth');
		// 创建表
		return $this->dbforge->create_table('stocks', TRUE);
	}

	/**
	 * 更新股票名称，有则update，无则insert
	 */
	public function replaceStockName($data) {
		$flag = $this->db->get_where('stocks', array('symbol' => $data['symbol']))->result_array();
		if ($flag) {
			$this->db->where(array('symbol' => $data['symbol']))->update('stocks', $data);
		} else {
			$this->db->insert('stocks', $data);
		}
	}
	/**
	 * [getByID 获取指定id的代码信息]
	 * @param  integer $sid [description]
	 * @return [type]       [description]
	 */
	public function getByID($sid = 1) {
		return $this->db->get_where('stocks', array('sid' => $sid))->result_array();
	}

	/**
	 * [getByScode 获取指定id的代码信息]
	 * @param  integer $sid [description]
	 * @return [type]       [description]
	 */
	public function getByCode($scode = '300314') {
		return $this->db->get_where('stocks', array('scode' => $scode))->result_array();
	}

	/**
	 * 更新指定序号的标记
	 */
	public function setFlag($scode = 935, $flag) {
		$data = array(
			'isStock' => $flag,
		);
		$this->db->where('scode', $scode)->update('stocks', $data);
	}

	/**
	 * 更新指定序号的标记
	 */
	public function setFlagBySymbol($symbol = 'SH000826', $flag) {
		$data = array(
			'isStock' => $flag,
		);
		$this->db->where('symbol', $symbol)->update('stocks', $data);
	}
	/**
	 * 获得数据库的总规模
	 */
	public function getTotalNumber() {
		return $this->db->count_all_results('stocks');
	}

	/**
	 * 返回所有合格股票代码
	 */
	public function getTotalCode() {
		return $this->db->order_by('scode', 'desc')->select('scode')->get_where('stocks', array('isStock' => 1))->result_array();
	}

	/**
	 * 获取符合要求的股票数量
	 */
	public function overview() {
		return $this->db->get_where('stocks', array('isStock' => 1))->num_rows();
		// codeignite3.0将属性num_rows改为方法num_rows()。
	}

	/**
	 * 返回所有公司标记为0的代码信息
	 */
	public function getAllFlag0() {
		return $this->db->order_by('scode', 'desc')->get_where('stocks', array('isStock' => 0))->result_array();
	}

	/**
	 * 标识缺失数据表的公司
	 */
	public function setNoTableFlag($scode = '') {
		$data = array(
			'noTable' => 1,
		);
		$this->db->where(array('scode' => $scode, 'isStock' => 1))->update('stocks', $data);
	}

	/**
	 * 标记连续增长的公司
	 */
	public function setGrowthingFlag($symbol, $flag) {
		$data = array('isGrowthing' => $flag);
		$this->db->where(array('scode' => $symbol))->update('stocks', $data);
	}

	/**
	 * 获取所有连续增长公司
	 */
	public function totalGrowthing($a = 4, $b = 0, $c = -2) {
		$sql = "SELECT * FROM stocks WHERE sustainedGrowth >= ? AND xsmlv >= ? AND jlrhjl >= ? AND isStock = 1 ORDER BY sustainedGrowth DESC, xsmlv DESC, jlrhjl DESC";
		return $this->db->query($sql, array($a, $b, $c))->result_array();
	}

	/**
	 * 获取自选股
	 */
	public function getSelectedStocks() {
		$sql = "SELECT * FROM stocks WHERE isSelected = 1 ORDER BY sustainedGrowth DESC, xsmlv DESC, jlrhjl DESC";
		return $this->db->query($sql)->result_array();
	}

	/**
	 * 存储连续高增长的季度数
	 */
	public function updateExtraInfo($scode, $number, $xsmlv, $jlrhjl) {
		$data = array(
			'sustainedGrowth' => $number,
			'xsmlv' => $xsmlv,
			'jlrhjl' => $jlrhjl,
		);
		$this->db->where(array('scode' => $scode))->update('stocks', $data);
	}

	/**
	 * 获取利润率最高的公司信息
	 */
	public function getHighProfit($number = 200) {
		$sql = "SELECT * FROM stocks ORDER BY xsmlv DESC, sustainedGrowth DESC, jlrhjl DESC LIMIT ?";
		return $this->db->query($sql, array($number))->result_array();
	}

	/**
	 * 更新市盈率数据
	 */
	public function updatePE($symbol, $pe) {
		$data = array('pe' => $pe);
		$this->db->where(array('scode' => $symbol))->update('stocks', $data);
	}

	/**
	 * 添加股票到自选
	 */
	public function updateSelectedFlag($symbol, $flag) {
		$data = array('isSelected' => $flag);
		$this->db->where(array('symbol' => $symbol))->update('stocks', $data);
	}

	/**
	 * 更新指定股票的历史pb高低值
	 */
	public function updateSelectedpb($scode, $lowpb, $toppb) {
		$data = array('lowpb' => $lowpb, 'toppb' => $toppb);
		$this->db->where(array('isSelected' => 1, 'scode' => $scode))->update('stocks', $data);
	}

	/**
	 * 获取指定股票用来设定网格交易的数据
	 */
	public function getGridinfo($scode) {
		$sql = "SELECT sname,symbol,lowpb,toppb,gridInterval FROM stocks WHERE scode = ? AND isStock = 1";
		return $this->db->query($sql, array($scode))->result_array();
	}

	/**
	 * 更新指定股票的网格间距
	 */
	public function updateGridInterval($symbol, $gridInterval) {
		$data = array('gridInterval' => $gridInterval);
		$this->db->where(array('symbol' => $symbol))->update('stocks', $data);
	}

	/**
	 * 更新指定股票的自选股状态
	 */
	public function updateSelected($scode, $flag) {
		$data = array('isSelected' => $flag);
		$this->db->where(array('isStock' => 1, 'scode' => $scode))->update('stocks', $data);
	}

	public function updateMgjzcByScode($scode, $mgjzc = 0) {
		if (is_null($mgjzc)) {
			$mgjzc = 0;
		}

		set_time_limit(0);
		$data = array('mgjzc' => $mgjzc);
		$this->db->where(array('isStock' => 1, 'scode' => $scode))->update('stocks', $data);
	}
}
